![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Using ANALYZE with the COMPUTE STATISTICS option scans the entire table, cluster, or index and computes exact statistics. When you compute the exact statistics, the resultant data is more accurate than that achieved by estimating the statistics; however, you use much more system resources to get this information. When you compute statistics for tables and clusters, you must have enough temporary space to load and sort the entire table or cluster. You do not need this temporary space for indexes. Using ANALYZE To Estimate Statistics When you use the ANALYZE command to estimate statistics, Oracle does much less work and uses much less temporary space. To run the ANALYZE command to estimate statistics, use one of the following syntaxes. For Tables: ANALYZE TABLE table_name ESTIMATE STATISTICS; For Clusters: ANALYZE CLUSTER cluster_name ESTIMATE STATISTICS; For Indexes: ANALYZE INDEX index_name ESTIMATE STATISTICS; When you use ANALYZE with the ESTIMATE STATISTICS option, Oracle scans a portion of the table, cluster, or index and computes estimated statistics. You can specify the amount of data scanned and used for statistics by including one or both of these additional parameters: SAMPLE xxxx ROWS; SAMPLE yy PERCENT; Place the SAMPLE xxxx ROWS parameter at the end of the ANALYZE command, as follows: ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 10000 ROWS; Place the SAMPLE yy PERCENT parameter at the end of the ANALYZE command, as follows: ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 40 PERCENT; Although estimating statistics does not give you as accurate a representation as computing statistics does, the lesser amount of resources consumed usually makes estimating statistics a better choice. By making the percentage of data scanned as large as possible for your system, you can increase the effectiveness of the statistics you gather. Using ANALYZE To Check Structural Integrity In addition to gather statistics, you can use the ANALYZE command to validate the structure of a table, cluster, or index. You should run this command only if you feel that there is some problem with the structure of these objects. These problems can occur as the result of a hardware or software problem that caused data corruption. By analyzing the structure of the schema objects, you can find any problems immediately and avoid a system crash. The ANALYZE command can be used in this manner with one of the following syntaxes: For Tables: ANALYZE TABLE table_name VALIDATE STRUCTURE; For Clusters: ANALYZE CLUSTER cluster_name VALIDATE STRUCTURE; For Indexes: ANALYZE INDEX index_name VALIDATE STRUCTURE; Adding the CASCADE option to the ANALYZE command results in the structure of all related tables being analyzed as well. Use the following syntax: ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE; When you analyze the integrity of the structure of tables, clusters, or indexes, the command returns any structural problems. If there are problems with structure of these objects, you should drop the object, re-create it, and reload the data. Using ANALYZE To Determine Chained Rows You can also use the ANALYZE command to determine the extent and existence of chained or migrated rows in your table or cluster. The existence of chained or migrated rows (as described in Chapter 10, Performance Enhancements), if significant, can cause severe performance degradation and should be corrected. Use the ANALYZE command in this manner with one of the following syntaxes: For Tables: ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows; For Clusters: ANALYZE CLUSTER cluster_name LIST CHAINED ROWS INTO chained_rows; The chained_rows table is a table with the proper structure to hold the information returned from the ANALYZE command. You can easily create the chained_rows table by using the UTLCHAIN.SQL script distributed with Oracle.
Data Dictionary StatisticsWhen you use the ANALYZE command to create statistics for the cost-based optimizer to use, these statistics are inserted into some internal Oracle performance tables. These tables can be queried through several views. Although these views provide essentially the same information, depending on the particular view you choose, the scope of the information changes slightly. The following views are prefixed with the following characters:
These views provide information about different parts of the system, such as tables, clusters, indexes, and columns. Following is a brief list of the views available that contain performance information:
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |